package cn.chint.manage.dao.impl;

import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.apache.commons.codec.net.QCodec;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.aggregation.ConditionalOperators.IfNull;
import org.springframework.orm.hibernate4.HibernateCallback;
import org.springframework.orm.hibernate4.HibernateTemplate;
import org.springframework.stereotype.Repository;

import com.google.common.base.FinalizablePhantomReference;

import cn.chint.bo.MsgEnterPages;
import cn.chint.bo.MsgEnterTempbo;
import cn.chint.bo.OrderOperatePages;
import cn.chint.bo.Ordersbo;
import cn.chint.po.MsgEnter;
import cn.chint.po.NoticeUser;
import cn.chint.po.Orders;
@Repository
public class OrderOperateDaoImpl implements cn.chint.manage.dao.OrderOperateDao {
	@Autowired
	HibernateTemplate hibernateTemplate;
	public Logger log = Logger.getLogger(getClass());

	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public OrderOperatePages find_orderlist(final int page,final int size) {
		// TODO Auto-generated method stub
		
		List<Ordersbo> ls = (List<Ordersbo>) this.hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createSQLQuery("select o.*,t.wx_avatar_url,t.wx_nickname from orders o INNER JOIN t_user t ON o.openid = t.wx_open_id ORDER BY id").
						addScalar("id",StandardBasicTypes.LONG).
					    addScalar("project_id",StandardBasicTypes.LONG).
					    addScalar("project_name",StandardBasicTypes.STRING).
					    addScalar("orderNo",StandardBasicTypes.STRING).
					    addScalar("paymentNo",StandardBasicTypes.STRING).
					    addScalar("openid",StandardBasicTypes.STRING).
					    addScalar("type",StandardBasicTypes.STRING). 
					    addScalar("describe_info",StandardBasicTypes.STRING).
					    addScalar("msg",StandardBasicTypes.STRING).
					    addScalar("amount",StandardBasicTypes.BIG_DECIMAL).
					    addScalar("order_status",StandardBasicTypes.INTEGER).
					    addScalar("createtime",StandardBasicTypes.STRING).
					    addScalar("wx_nickname",StandardBasicTypes.STRING).
					    addScalar("wx_avatar_url",StandardBasicTypes.STRING).
					    setResultTransformer(Transformers.aliasToBean(Ordersbo.class)).setFirstResult((page-1)*size).setMaxResults(size).list();
			}
		});
		
		@SuppressWarnings("unchecked")
		BigInteger count = (BigInteger)this.hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createSQLQuery("select count(*) from orders t").uniqueResult();			
			}
		
		});
		@SuppressWarnings("unchecked")
		BigDecimal total_amount = (BigDecimal)this.hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createSQLQuery("SELECT sum(amount) from orders where order_status=2").uniqueResult();			
			}
		
		});
		OrderOperatePages  orderOperatePages =new OrderOperatePages();
		orderOperatePages.setLs(ls);
		orderOperatePages.setOrder_num(count.intValue());
		orderOperatePages.setTotal_amount(total_amount);
		return orderOperatePages;
		
	}
	
	@SuppressWarnings({ "unchecked", "rawtypes" })
	@Override
	public OrderOperatePages find_order_by_name(final int page,final int size,final String nickname,final String startTime,final String endTime) {
		// TODO Auto-generated method stub

		@SuppressWarnings("unchecked")
		final BigInteger count = (BigInteger)this.hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				StringBuffer c=new StringBuffer();
				c.append("select count(*)  from orders o INNER JOIN t_user t ON o.openid = t.wx_open_id where 1=1 ");
				if(!("").equals(nickname)) {
					c.append("and wx_nickname='"+nickname+"'");
				}
				if(!("").equals(startTime)) {
					c.append("and o.createTime between'"+startTime+"'and'"+endTime+" 23:59:59' ");
				}
				return session.createSQLQuery(c.toString()).uniqueResult();
			}

		});
		final int cou=count.intValue();
		List<Ordersbo> ls = (List<Ordersbo>) this.hibernateTemplate.execute(new HibernateCallback() {
			@SuppressWarnings("unused")
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				StringBuffer s=new StringBuffer();
				log.info(startTime);
				log.info(endTime);
				log.info("nickname"+nickname);
				s.append("select o.*,t.wx_avatar_url,t.wx_nickname from orders o INNER JOIN t_user t ON o.openid = t.wx_open_id where 1=1 ");
				if(!("").equals(nickname)) {
						s.append("and wx_nickname='"+nickname+"'");
				}
				if(!("").equals(startTime)) {
					s.append("and o.createTime between '"+startTime+"'and'"+endTime+" 23:59:59'");
				}
				s.append("ORDER BY id");
				int page1=page;
				if(cou/size<page){
					page1=cou/size+1;
				}
				return session.createSQLQuery(s.toString()).
						addScalar("id",StandardBasicTypes.LONG).
					    addScalar("project_id",StandardBasicTypes.LONG).
					    addScalar("project_name",StandardBasicTypes.STRING).
					    addScalar("orderNo",StandardBasicTypes.STRING).
					    addScalar("paymentNo",StandardBasicTypes.STRING).
					    addScalar("openid",StandardBasicTypes.STRING).
					    addScalar("type",StandardBasicTypes.STRING). 
					    addScalar("describe_info",StandardBasicTypes.STRING).
					    addScalar("msg",StandardBasicTypes.STRING).
					    addScalar("amount",StandardBasicTypes.BIG_DECIMAL).
					    addScalar("order_status",StandardBasicTypes.INTEGER).
					    addScalar("createtime",StandardBasicTypes.STRING).
					    addScalar("wx_nickname",StandardBasicTypes.STRING).
					    addScalar("wx_avatar_url",StandardBasicTypes.STRING).
					    setResultTransformer(Transformers.aliasToBean(Ordersbo.class)).setFirstResult((page1-1)*size).setMaxResults(size).list();
			}
		});
		

		@SuppressWarnings("unchecked")
		BigDecimal total_amount = (BigDecimal)this.hibernateTemplate.execute(new HibernateCallback() {
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				return session.createSQLQuery("SELECT sum(amount) from orders where order_status=2").uniqueResult();			
			}
		
		});
		@SuppressWarnings("unchecked")
		BigDecimal find_amount = (BigDecimal)this.hibernateTemplate.execute(new HibernateCallback() {
			@SuppressWarnings("unused")
			@Override
			public Object doInHibernate(Session session) throws HibernateException {
				StringBuffer p=new StringBuffer();
				p.append("select sum(amount)  from orders o INNER JOIN t_user t ON o.openid = t.wx_open_id WHERE 1=1 ");
				if(!("").equals(nickname)) {
						p.append("and wx_nickname='"+nickname+"'");
				}
				if(!("").equals(startTime)) {
					p.append("and o.createTime between'"+startTime+"'and'"+endTime+" 23:59:59' ");
				}
				
				p.append("and order_status=2");
				
				return session.createSQLQuery(p.toString()).uniqueResult();			
			}
		
		});
		OrderOperatePages  orderOperatePages =new OrderOperatePages();
		orderOperatePages.setLs(ls);
		orderOperatePages.setOrder_num(count.intValue());
		orderOperatePages.setTotal_amount(total_amount);
		orderOperatePages.setFind_amount(find_amount);
		log.info(orderOperatePages.getOrder_num());
		return orderOperatePages;
		
	}
	
}


















